package com.ruoyi.dev.service.impl;

import cn.hutool.db.*;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.ruoyi.common.annotation.DataScope;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.exception.ServiceException;
import com.ruoyi.common.utils.*;
import com.ruoyi.dev.domain.DevDataset;
import com.ruoyi.dev.domain.DevModel;
import com.ruoyi.dev.service.IDevDatasetService;
import com.ruoyi.dev.service.IDevModelDataService;
import com.ruoyi.dev.service.IDevModelService;
import com.ruoyi.dev.vo.DbField;
import com.ruoyi.dev.vo.DbWhere;
import com.ruoyi.dev.vo.QueryVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.*;

/**
 * 模型数据Service接口
 *
 * @author yepanpan
 * @date 2025-01-21
 */
@Service
@Slf4j
public class DevModelDataServiceImpl implements IDevModelDataService {
    @Autowired
    private IDevModelService devModelService;
    @Autowired
    private IDevDatasetService devDatasetService;
    @Autowired
    private DataSource localDs;

    @Override
    @DataScope(deptAlias = "t", userAlias = "t")
    public Object queryDs(DevDataset devDataset) {
        DevDataset ds = devDatasetService.selectDevDatasetByCode(devDataset.getCode());
        if(ds == null){
            throw new ServiceException("非法请求!");
        }
        String sql = ds.getSqls();

        //权限范围
        sql = sql.replaceAll("\\$\\{dataScope\\}", devDataset.getParams().get("dataScope").toString());
        //当前用户
        SysUser sysUser = SecurityUtils.getLoginUser().getUser();
        sql = sql.replaceAll("\\$\\{userId\\}", sysUser.getUserId().toString());
        sql = sql.replaceAll("\\$\\{deptId\\}", sysUser.getDeptId().toString());

        //参数
        Object pv = devDataset.getParams().get(ds.getSearchField());
        if(pv != null && StringUtils.isNotEmpty(pv.toString())){
            String sv = pv.toString();
            if("range".equalsIgnoreCase(ds.getSearchExp())){
                sql = "SELECT * FROM ( "+sql+" ) t WHERE "+ds.getSearchField()+" between " + sv.replace(",", " AND ");
            }else{
                sql = "SELECT * FROM ( "+sql+" ) t WHERE "+ds.getSearchField()+" = " + sv;
            }
        }

        try {
            List<Entity> list = Db.use(localDs).page(sql, new Page(0, 1000));
            if(DevDataset.TYPE_SUM.equalsIgnoreCase(ds.getType())){
                //如果是汇总计算查询，返回第一个结果，没有结果返回0
                return list.isEmpty() ? 0 : list.get(0).values().stream().findFirst().get();
            }else if(DevDataset.TYPE_MAP.equalsIgnoreCase(ds.getType())){
                //如果是映射，就生成label->value的map
                Entity data = new Entity();
                list.forEach(e->{
                    data.put(e.getStr("label"), e.get("value"));
                });
                return data;
            }else{
                return list;
            }
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 查询模型数据
     *
     * @param modelCode 数据模型代码
     * @param id 查询参数
     * @return 数据模型集合
     */
    @Override
    public Entity selectDevModelDataById(String modelCode, Long id){
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }
        QueryVo querySql = QueryVo.createByTable(modelCode);
        querySql.eq(devModel.getPkField(), id);
        try {
            JSON.configWriterDateFormat(DateUtils.YYYY_MM_DD_HH_MM_SS);
            Entity data = Db.use(localDs).queryOne(querySql.getSql(), querySql.getParams());
            return data;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public PageResult<Entity> selectDevModelDataList(String modelCode, DbWhere params, Page page) {
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }
        QueryVo querySql = QueryVo.createByTable(devModel.getCode());
        if(params != null && StringUtils.isNotEmpty(params.getExp())){
            querySql.where(params.getField(), params.getExp(), params.getVal());
        }
        try{
            if(page == null){
                page = new Page(0, 10);
            }
            if(querySql.getParams().isEmpty()){
                return Db.use(localDs).page(querySql.getSql(), page);
            }else{
                return Db.use(localDs).page(querySql.getSql(), page, querySql.getParams());
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int insertDevModelData(String modelCode, Map<String, Object> data) {
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }

        Entity entity = new Entity(devModel.getCode());
        for(DbField field: devModel.getFields()){
            Object v = data.get(field.getCode());
            //空值时，判断是否有默认值
            if(v == null || StringUtils.isEmpty(v.toString())){
                if(field.getRequired().equalsIgnoreCase("Y")){
                    throw new ServiceException(field.getName() + " 不能为空");
                }
                continue;
            }

            //数据验证
            v = valid(field, v);
            entity.put(field.getCode(), v);
        }
        try{
            Db db = Db.use(localDs);
            if(checkDataUnique(devModel, entity, db) == null){
                insertModelData(devModel, entity, Db.use(localDs));
            }else{
                throw new ServiceException("数据重复");
            }
            return 1;
        }catch (Exception e){
            e.printStackTrace();
            throw new ServiceException(e.getMessage());
        }
    }

    @Override
    public int updateDevModelData(String modelCode, Map<String, Object> data) {
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }

        Entity entity = new Entity(devModel.getCode());
        for(DbField field: devModel.getFields()){
            Object v = data.get(field.getCode());
            //空值不处理
            if(v == null || StringUtils.isEmpty(v.toString())){
                continue;
            }

            v = valid(field, v);
            entity.put(field.getCode(), v);
        }

        try{
            Db db = Db.use(localDs);
            Entity old = checkDataUnique(devModel, entity, db);
            if(old == null || old.getStr(devModel.getPkField()).equalsIgnoreCase(entity.getStr(devModel.getPkField()))) {
                updateModelData(devModel, entity, Db.use(localDs));
            }else{
                throw new ServiceException("数据重复!");
            }
            return 1;
        }catch (Exception e){
            e.printStackTrace();
            throw new ServiceException(e.getMessage());
        }
    }

    @Override
    public int deleteDevModelDataByIds(String modelCode, Long[] ids) {
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }

        try{
            Db db = Db.use(localDs);
            return db.del(devModel.getCode(), devModel.getPkField(), ids);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteDevModelDataById(String modelCode, Long id) {
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }

        try{
            Db db = Db.use(localDs);
            return db.del(devModel.getCode(), devModel.getPkField(), id);
        }catch (Exception e){
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 验证数据，日期转换/正则验证
     * @param field
     * @param v
     * @return
     */
    public Object valid(DbField field, Object v){
        //非空时，日期时间要转换类型
        if(field.getType().equalsIgnoreCase("date") || field.getType().equalsIgnoreCase("datetime")){
            if(!(v instanceof Date)){
                v = DateUtils.parseDate(v);
            }
            if(v == null){
                throw new ServiceException(field.getName() + " 格式有误");
            }
        }else {
            //验证
            if (field.getLengths() != null && field.getLengths() > 0){
                if(v.toString().length() > field.getLengths()){
                    throw new ServiceException(field.getName() + " 长度不能大于 " + field.getLengths());
                }
            }
        }
        return v;
    }

    /**
     * 删除验证，如果结果是为或者0。可以删除
     * @param db
     * @param checkSql
     * @param id
     * @return
     * @throws SQLException
     */
    public boolean delCheck(Db db, String checkSql, Long id) throws SQLException {
        int ret = 0;
        String[] sqls = checkSql.split(";");

        for (String sql: sqls){
            Number rows = db.queryNumber(sql.replaceAll("\\$\\{id\\}", id.toString())).intValue();
            if(rows != null){
                ret += rows.intValue();
            }
        }
        return ret == 0;
    }


    /**
     *
     * @param devModel
     * @param entity
     * @param db
     * @return
     * @throws SQLException
     */
    public Entity checkDataUnique(DevModel devModel, Entity entity, Db db) throws SQLException {
        QueryVo querySql = QueryVo.createByTable(devModel.getCode());
        //唯一键重复
        if(StringUtils.isNotEmpty(devModel.getUniqueField())){
            String[] fields = devModel.getUniqueField().split(",");
            for(String f:fields){
                Object v = entity.get(f);
                if(v == null){
                    querySql.nl(f);
                }else{
                    querySql.eq(f, v);
                }
            }
            Entity old = db.queryOne(querySql.getSql(), querySql.getParams());
            if(old != null){
                return old;
            }
        }

        querySql.clear();
        //主键重复
        if(entity.get(devModel.getPkField()) != null) {
            querySql.eq(devModel.getPkField(), entity.get(devModel.getPkField()));
            Entity old = db.queryOne(querySql.getSql(), querySql.getParams());
            if(old != null){
                return old;
            }
        }
        return null;
    }

    /**
     * 更新模型数据
     * @param devModel
     * @param entity
     * @param db
     * @throws SQLException
     */
    private void updateModelData(DevModel devModel, Entity entity, Db db) throws SQLException {
        db.tx((db1)->{

            Entity where = new Entity();
            where.put(devModel.getPkField(), entity.get(devModel.getPkField()));
            db1.update(entity, where);

        });
    }

    /**
     * 新增模型数据
     * @param devModel
     * @param entity
     * @param db
     * @throws SQLException
     */
    private void insertModelData(DevModel devModel, Entity entity, Db db) throws SQLException {
        db.tx((db1)->{

            Long id = db1.insertForGeneratedKey(entity);
            entity.put(devModel.getPkField(), id);

        });
    }

    /**
     * 生成导入模板
     * @param response
     * @param modelCode
     */
    @Override
    public void importTemplateExcel(HttpServletResponse response, String modelCode){
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }
        Db db = Db.use(localDs);
        List<ExcelUtil.ExcelField> importFields = excelFields(devModel.getFields());
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            ExcelUtil.template(importFields, response.getOutputStream());
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 导出数据
     * @param response
     * @param modelCode
     * @param params
     */
    @Override
    public void exportExcel(HttpServletResponse response, String modelCode, DbWhere params){
        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }
        List<ExcelUtil.ExcelField> exportFields = excelFields(devModel.getFields());
        PageResult<Entity> pageResult = selectDevModelDataList(modelCode, params, new Page(0, 10000));
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            List<Map<String,Object>> list = new ArrayList<>();
            if(pageResult != null && !pageResult.isEmpty()){
                for(Entity row:pageResult){
                    Map<String, Object> data = new HashMap<>();
                    data.putAll(row);
                    list.add(data);
                }
            }
            ExcelUtil.exports(exportFields, list, response.getOutputStream());
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public String importData(String modelCode, InputStream inputStream, Boolean isUpdateSupport, String operName) {

        DevModel devModel = devModelService.selectDevModelDetail(modelCode);
        if(devModel == null){
            throw new ServiceException("非法请求!");
        }

        Db db = Db.use(localDs);
        List<ExcelUtil.ExcelField> importFields = excelFields(devModel.getFields());

        //解析数据
        List<Map<String, Object>> dataList = ExcelUtil.read(importFields, inputStream, 1,0);
        if (StringUtils.isNull(dataList) || dataList.size() == 0)
        {
            throw new ServiceException("导入数据不能为空！");
        }

        int successNum = 0;
        int failureNum = 0;
        StringBuilder successMsg = new StringBuilder();
        StringBuilder failureMsg = new StringBuilder();
        for (int r=0; r<dataList.size(); r++)
        {
            Map<String, Object> row = dataList.get(r);
            try
            {
                Entity entity = new Entity(devModel.getCode());
                for(int i = 0; i<devModel.getFields().size(); i++){
                    DbField field = devModel.getFields().get(i);
                    if(field.getType().equalsIgnoreCase("view")){
                        continue;
                    }
                    Object v = row.get(field.getCode());
                    if(v == null || StringUtils.isEmpty(v.toString())){
                        if(field.getRequired().equalsIgnoreCase("Y")){
                            throw new ServiceException(field.getName() + " 不能为空");
                        }
                        continue;
                    }
                    //非空时，日期时间要转换类型
                    if(field.getType().equalsIgnoreCase("date") || field.getType().equalsIgnoreCase("datetime")){
                        if(v instanceof Date){
                            Date vo = DateUtils.parseDate(v);
                            if(vo == null){
                                throw new ServiceException(field.getName() + " 格式有误");
                            }else{
                                entity.put(field.getCode(), vo);
                            }
                        }else{
                            entity.put(field.getCode(), v);
                        }

                    }else {
                        //验证
                        String vs = v.toString();
                        if (field.getLengths() != null && field.getLengths() > 0){
                            if(vs.length() > field.getLengths()){
                                throw new ServiceException(field.getName() + " 长度不能大于 " + field.getLengths());
                            }
                        }
                        entity.put(field.getCode(), v);
                    }
                }
                Entity old = checkDataUnique(devModel, entity, db);
                if(old == null){
                    insertModelData(devModel, entity, db);
                }else if(isUpdateSupport){
                    entity.put(devModel.getPkField(), old.get(devModel.getPkField()));
                    updateModelData(devModel, entity, db);
                }else{
                    throw new ServiceException("数据重复");
                }
                successNum ++;
            }
            catch (Exception e)
            {
                failureNum++;
                String msg = "<br/>" + failureNum + "、"+r+"行数据 导入失败：";
                failureMsg.append(msg + e.getMessage());
                log.error(msg, e);
            }
        }
        if (failureNum > 0)
        {
            failureMsg.insert(0, "很抱歉，导入失败！共 " + failureNum + " 条数据格式不正确，错误如下：");
            throw new ServiceException(failureMsg.toString());
        }
        else
        {
            successMsg.insert(0, "恭喜您，数据已全部导入成功！共 " + successNum + " 条，数据如下：");
        }
        return successMsg.toString();
    }

    /**
     * 生成Excel导入导出字段
     * @param devModelFields
     * @return
     */
    public List<ExcelUtil.ExcelField> excelFields(List<DbField> devModelFields){
        List<ExcelUtil.ExcelField> fieldList = new ArrayList<>();
        int index = 0;
        for(DbField f:devModelFields){
            ExcelUtil.ExcelField field = new ExcelUtil.ExcelField();
            field.setCode(f.getCode());
            field.setLabel(f.getName());
            field.setIndex(index++);
            fieldList.add(field);
        }
        return fieldList;
    }
}
